<!DOCTYPE html>
<html lang="zh-CN">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta name="robots" content="noodp" />
        <title>MySql 常用命令 - BYTESC blog</title><meta name="Description" content="这是我的全新 Hugo 网站"><meta property="og:title" content="MySql 常用命令" />
<meta property="og:description" content="记录一些 MySql 常用命令" />
<meta property="og:type" content="article" />
<meta property="og:url" content="http://example.org/mysql%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/" /><meta property="og:image" content="http://example.org/logo.png"/><meta property="article:section" content="posts" />
<meta property="article:published_time" content="2023-09-18T15:58:26+08:00" />
<meta property="article:modified_time" content="2023-09-18T15:58:26+08:00" /><meta property="og:site_name" content="我的网站" />
<meta name="twitter:card" content="summary_large_image"/>
<meta name="twitter:image" content="http://example.org/logo.png"/>

<meta name="twitter:title" content="MySql 常用命令"/>
<meta name="twitter:description" content="记录一些 MySql 常用命令"/>
<meta name="application-name" content="我的网站">
<meta name="apple-mobile-web-app-title" content="我的网站"><meta name="theme-color" content="#ffffff"><meta name="msapplication-TileColor" content="#da532c"><link rel="shortcut icon" type="image/x-icon" href="/favicon.ico" />
        <link rel="icon" type="image/png" sizes="32x32" href="/favicon-32x32.png">
        <link rel="icon" type="image/png" sizes="16x16" href="/favicon-16x16.png"><link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png"><link rel="mask-icon" href="/safari-pinned-tab.svg" color="#5bbad5"><link rel="manifest" href="/site.webmanifest"><link rel="canonical" href="http://example.org/mysql%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/" /><link rel="prev" href="http://example.org/linux-swap%E5%88%86%E5%8C%BA/" /><link rel="next" href="http://example.org/git%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/" /><link rel="stylesheet" href="/css/style.min.css"><link rel="preload" href="/lib/fontawesome-free/all.min.css" as="style" onload="this.onload=null;this.rel='stylesheet'">
        <noscript><link rel="stylesheet" href="/lib/fontawesome-free/all.min.css"></noscript><link rel="preload" href="/lib/animate/animate.min.css" as="style" onload="this.onload=null;this.rel='stylesheet'">
        <noscript><link rel="stylesheet" href="/lib/animate/animate.min.css"></noscript><script type="application/ld+json">
    {
        "@context": "http://schema.org",
        "@type": "BlogPosting",
        "headline": "MySql 常用命令",
        "inLanguage": "zh-CN",
        "mainEntityOfPage": {
            "@type": "WebPage",
            "@id": "http:\/\/example.org\/mysql%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4\/"
        },"genre": "posts","keywords": "Mysql","wordcount":  5659 ,
        "url": "http:\/\/example.org\/mysql%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4\/","datePublished": "2023-09-18T15:58:26+08:00","dateModified": "2023-09-18T15:58:26+08:00","license": "bytesc","publisher": {
            "@type": "Organization",
            "name": ""},"author": {
                "@type": "Person",
                "name": "bytesc"
            },"description": ""
    }
    </script></head>
    <body data-header-desktop="fixed" data-header-mobile="auto"><script type="text/javascript">(window.localStorage && localStorage.getItem('theme') ? localStorage.getItem('theme') === 'dark' : ('dark' === 'auto' ? window.matchMedia('(prefers-color-scheme: dark)').matches : 'dark' === 'dark')) && document.body.setAttribute('theme', 'dark');</script>

        <div id="mask"></div><div class="wrapper"><header class="desktop" id="header-desktop">
    <div class="header-wrapper">
        <div class="header-title">
            <a href="/" title="BYTESC blog"><span id="id-1" class="typeit"></span></a>
        </div>
        <div class="menu">
            <div class="menu-inner"><a class="menu-item" href="/"> 首页 </a><a class="menu-item" href="/posts/"> 文章 </a><a class="menu-item" href="/tags/"> 标签 </a><a class="menu-item" href="/categories/"> 分类 </a><a class="menu-item" href="/about/"> 关于 </a><span class="menu-item delimiter"></span><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                    <i class="fas fa-adjust fa-fw" aria-hidden="true"></i>
                </a></div>
        </div>
    </div>
</header><header class="mobile" id="header-mobile">
    <div class="header-container">
        <div class="header-wrapper">
            <div class="header-title">
                <a href="/" title="BYTESC blog"><span id="id-2" class="typeit"></span></a>
            </div>
            <div class="menu-toggle" id="menu-toggle-mobile">
                <span></span><span></span><span></span>
            </div>
        </div>
        <div class="menu" id="menu-mobile"><a class="menu-item" href="/" title="">首页</a><a class="menu-item" href="/posts/" title="">文章</a><a class="menu-item" href="/tags/" title="">标签</a><a class="menu-item" href="/categories/" title="">分类</a><a class="menu-item" href="/about/" title="">关于</a><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                <i class="fas fa-adjust fa-fw" aria-hidden="true"></i>
            </a></div>
    </div>
</header><main class="main">
                <div class="container"><div class="toc" id="toc-auto">
            <h2 class="toc-title">目录</h2>
            <div class="toc-content" id="toc-content-auto"></div>
        </div><article class="page single"><h1 class="single-title animate__animated animate__flipInX">MySql 常用命令</h1><h2 class="single-subtitle">记录一些 MySql 常用命令</h2><div class="post-meta">
            <div class="post-meta-line"><span class="post-author"><a href="/" title="Author" rel="author" class="author"><i class="fas fa-user-circle fa-fw" aria-hidden="true"></i>bytesc</a></span>&nbsp;<span class="post-category">收录于 <a href="/categories/%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/"><i class="far fa-folder fa-fw" aria-hidden="true"></i>常用命令</a></span></div>
            <div class="post-meta-line"><i class="far fa-calendar-alt fa-fw" aria-hidden="true"></i>&nbsp;<time datetime="2023-09-18">2023-09-18</time>&nbsp;<i class="fas fa-pencil-alt fa-fw" aria-hidden="true"></i>&nbsp;约 5659 字&nbsp;
                <i class="far fa-clock fa-fw" aria-hidden="true"></i>&nbsp;预计阅读 12 分钟&nbsp;</div>
        </div><div class="featured-image"><img
        class="lazyload"
        src="/svg/loading.min.svg"
        data-src="/images/cover/mysql.png"
        data-srcset="/images/cover/mysql.png, /images/cover/mysql.png 1.5x, /images/cover/mysql.png 2x"
        data-sizes="auto"
        alt="/images/cover/mysql.png"
        title="/images/cover/mysql.png" width="858" height="472" /></div><div class="details toc" id="toc-static"  data-kept="">
                <div class="details-summary toc-title">
                    <span>目录</span>
                    <span><i class="details-icon fas fa-angle-right" aria-hidden="true"></i></span>
                </div>
                <div class="details-content toc-content" id="toc-content-static"><nav id="TableOfContents">
  <ul>
    <li><a href="#基本操作">基本操作</a>
      <ul>
        <li><a href="#分隔符设置">分隔符设置</a></li>
        <li><a href="#执行sql文件">执行sql文件</a></li>
      </ul>
    </li>
    <li><a href="#ddl">DDL</a>
      <ul>
        <li><a href="#数据库">数据库</a></li>
        <li><a href="#表">表</a></li>
        <li><a href="#数据类型">数据类型</a></li>
      </ul>
    </li>
    <li><a href="#dml">DML</a></li>
    <li><a href="#dql">DQL</a>
      <ul>
        <li><a href="#查询语句">查询语句</a></li>
        <li><a href="#比较运算符">比较运算符</a></li>
        <li><a href="#聚合函数">聚合函数</a></li>
        <li><a href="#连表查询">连表查询</a></li>
      </ul>
    </li>
    <li><a href="#dcl">DCL</a>
      <ul>
        <li><a href="#用户管理">用户管理</a></li>
        <li><a href="#权限列表">权限列表</a></li>
        <li><a href="#权限管理">权限管理</a></li>
      </ul>
    </li>
    <li><a href="#流程控制">流程控制</a>
      <ul>
        <li><a href="#事务">事务</a></li>
        <li><a href="#视图">视图</a></li>
        <li><a href="#变量">变量</a></li>
        <li><a href="#临时表">临时表</a></li>
        <li><a href="#存储过程">存储过程</a></li>
        <li><a href="#异常处理">异常处理</a></li>
        <li><a href="#游标">游标</a></li>
        <li><a href="#存储函数">存储函数</a></li>
        <li><a href="#触发器">触发器</a></li>
        <li><a href="#mysql函数">MYSQL函数</a></li>
        <li><a href="#事件">事件</a></li>
      </ul>
    </li>
    <li><a href="#程序举例">程序举例</a>
      <ul>
        <li><a href="#银行转账存储过程">银行转账存储过程</a></li>
        <li><a href="#加法器存储函数">加法器存储函数</a></li>
        <li><a href="#清理垃圾事件">清理垃圾事件</a></li>
        <li><a href="#删除保护触发器">删除保护触发器</a></li>
      </ul>
    </li>
    <li><a href="#锁">锁</a>
      <ul>
        <li><a href="#全局锁">全局锁</a></li>
        <li><a href="#表锁">表锁</a></li>
        <li><a href="#行级锁">行级锁</a></li>
      </ul>
    </li>
    <li><a href="#数据库备份">数据库备份</a>
      <ul>
        <li><a href="#sql模式">sql模式</a></li>
        <li><a href="#txt模式">txt模式</a></li>
        <li><a href="#csv模式">csv模式</a></li>
        <li><a href="#日志">日志</a></li>
      </ul>
    </li>
  </ul>
</nav></div>
            </div><div class="content" id="content"><h1 id="mysql常用命令">MySql常用命令</h1>
<h2 id="基本操作">基本操作</h2>
<h3 id="分隔符设置">分隔符设置</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">DELIMITER</span><span class="w"> </span><span class="err">$$</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">DELIMITER</span><span class="w"> </span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="执行sql文件">执行sql文件</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl">mysql -uroot -ppassword -Dmy_database &lt; my_script.sql
</span></span><span class="line"><span class="cl"><span class="nb">source</span> my_script.sql<span class="p">;</span>
</span></span></code></pre></td></tr></table>
</div>
</div><h2 id="ddl">DDL</h2>
<h3 id="数据库">数据库</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">show</span><span class="w"> </span><span class="n">databases</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">select</span><span class="w"> </span><span class="k">database</span><span class="p">();</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">create</span><span class="w"> </span><span class="k">database</span><span class="w"> </span><span class="p">[</span><span class="k">if</span><span class="w"> </span><span class="k">not</span><span class="w"> </span><span class="k">exists</span><span class="p">]</span><span class="w"> </span><span class="err">数据库名</span><span class="w"> </span><span class="p">[</span><span class="k">default</span><span class="w"> </span><span class="n">charset</span><span class="w"> </span><span class="n">utf8mb4</span><span class="p">]</span><span class="w"> </span><span class="p">[</span><span class="k">collate</span><span class="w"> </span><span class="err">排序规则</span><span class="p">];</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">show</span><span class="w"> </span><span class="k">collation</span><span class="p">;</span><span class="w">   </span><span class="err">查看字符集</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">show</span><span class="w"> </span><span class="k">create</span><span class="w"> </span><span class="k">database</span><span class="w"> </span><span class="err">数据库名</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">drop</span><span class="w"> </span><span class="k">database</span><span class="w"> </span><span class="p">[</span><span class="k">if</span><span class="w"> </span><span class="k">exists</span><span class="p">]</span><span class="w"> </span><span class="err">数据库名</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">use</span><span class="w"> </span><span class="err">数据库名</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="表">表</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">show</span><span class="w"> </span><span class="n">tables</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">create</span><span class="w"> </span><span class="k">table</span><span class="w"> </span><span class="err">表名</span><span class="p">(</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="err">字段</span><span class="mi">1</span><span class="w"> </span><span class="err">数据类型</span><span class="p">[</span><span class="k">comment</span><span class="w"> </span><span class="err">注释内容</span><span class="p">][</span><span class="k">not</span><span class="w"> </span><span class="k">null</span><span class="p">][</span><span class="k">unique</span><span class="p">][</span><span class="k">default</span><span class="p">][</span><span class="k">check</span><span class="p">(</span><span class="err">条件表达式</span><span class="p">)][</span><span class="k">primary</span><span class="w"> </span><span class="k">key</span><span class="p">][</span><span class="k">foreign</span><span class="w"> </span><span class="k">key</span><span class="w"> </span><span class="p">(</span><span class="err">外键字段名</span><span class="p">)</span><span class="w"> </span><span class="k">references</span><span class="w"> </span><span class="err">主表名</span><span class="p">(</span><span class="err">列名</span><span class="p">)]</span><span class="w"> </span><span class="p">[</span><span class="n">auto_increment</span><span class="p">],</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="err">字段</span><span class="mi">2</span><span class="w"> </span><span class="err">数据类型</span><span class="p">[</span><span class="k">comment</span><span class="w"> </span><span class="err">注释内容</span><span class="p">],</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="n">age</span><span class="w"> </span><span class="nb">INT</span><span class="w"> </span><span class="n">unsigned</span><span class="w">  </span><span class="k">comment</span><span class="w"> </span><span class="s1">&#39;年龄&#39;</span><span class="w"> </span><span class="k">default</span><span class="w"> </span><span class="mi">18</span><span class="w"> </span><span class="k">check</span><span class="p">(</span><span class="n">age</span><span class="o">&gt;=</span><span class="mi">0</span><span class="w"> </span><span class="k">and</span><span class="w"> </span><span class="n">age</span><span class="o">&lt;=</span><span class="mi">150</span><span class="p">),</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="k">constraint</span><span class="w"> </span><span class="err">外键名称</span><span class="w"> </span><span class="k">foreign</span><span class="w"> </span><span class="k">key</span><span class="w"> </span><span class="p">(</span><span class="err">外键字段名</span><span class="p">)</span><span class="w"> </span><span class="k">references</span><span class="w"> </span><span class="err">主表名</span><span class="p">(</span><span class="err">列名</span><span class="p">)</span><span class="w"> </span><span class="p">[</span><span class="k">on</span><span class="w"> </span><span class="k">update</span><span class="o">|</span><span class="k">delete</span><span class="w"> </span><span class="k">restrict</span><span class="o">|</span><span class="k">cascade</span><span class="o">|</span><span class="k">set</span><span class="w"> </span><span class="k">null</span><span class="p">],</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="k">foreign</span><span class="w"> </span><span class="k">key</span><span class="w"> </span><span class="p">(</span><span class="err">外键字段名</span><span class="p">)</span><span class="w"> </span><span class="k">references</span><span class="w"> </span><span class="err">主表名</span><span class="p">(</span><span class="err">列名</span><span class="p">)</span><span class="w"> </span><span class="p">[</span><span class="k">on</span><span class="w"> </span><span class="k">update</span><span class="o">|</span><span class="k">delete</span><span class="w"> </span><span class="k">restrict</span><span class="o">|</span><span class="k">cascade</span><span class="o">|</span><span class="k">set</span><span class="w"> </span><span class="k">null</span><span class="p">],</span><span class="w">	
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="k">primary</span><span class="w"> </span><span class="k">key</span><span class="w"> </span><span class="p">(</span><span class="err">字段</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">字段</span><span class="mi">2</span><span class="p">),</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="p">[</span><span class="k">unique</span><span class="p">]</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="err">索引名</span><span class="p">(</span><span class="err">字段</span><span class="mi">1</span><span class="p">[(</span><span class="k">length</span><span class="p">)]</span><span class="w"> </span><span class="p">,</span><span class="w"> </span><span class="err">字段</span><span class="mi">2</span><span class="p">[(</span><span class="k">length</span><span class="p">)]</span><span class="w"> </span><span class="p">),</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="p">)[</span><span class="k">comment</span><span class="w"> </span><span class="err">表注释</span><span class="p">];</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">show</span><span class="w"> </span><span class="k">create</span><span class="w"> </span><span class="k">table</span><span class="w"> </span><span class="err">表名</span><span class="p">;</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">desc</span><span class="w"> </span><span class="err">表名</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">drop</span><span class="w"> </span><span class="k">table</span><span class="w"> </span><span class="p">[</span><span class="k">if</span><span class="w"> </span><span class="k">exists</span><span class="p">]</span><span class="w"> </span><span class="err">表名；</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">truncate</span><span class="w"> </span><span class="k">table</span><span class="w"> </span><span class="err">表名</span><span class="p">;</span><span class="w">  </span><span class="err">删除并创建一样的新表</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">alter</span><span class="w"> </span><span class="k">table</span><span class="w"> </span><span class="err">旧表名</span><span class="w"> </span><span class="k">rename</span><span class="w"> </span><span class="k">to</span><span class="w"> </span><span class="err">新表名</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">alter</span><span class="w"> </span><span class="k">table</span><span class="w"> </span><span class="err">表名</span><span class="w"> </span><span class="k">add</span><span class="w"> </span><span class="err">字段名</span><span class="w"> </span><span class="err">类型</span><span class="w"> </span><span class="p">[</span><span class="k">comment</span><span class="p">]</span><span class="w"> </span><span class="p">[</span><span class="err">约束</span><span class="p">];</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">alter</span><span class="w"> </span><span class="k">table</span><span class="w"> </span><span class="err">表名</span><span class="w"> </span><span class="k">add</span><span class="w"> </span><span class="k">constraint</span><span class="w"> </span><span class="err">外键名称</span><span class="w"> </span><span class="k">foreign</span><span class="w"> </span><span class="k">key</span><span class="w"> </span><span class="p">(</span><span class="err">外键字段名</span><span class="p">)</span><span class="w"> </span><span class="k">references</span><span class="w"> </span><span class="err">主表名</span><span class="p">(</span><span class="err">列名</span><span class="p">)</span><span class="w"> </span><span class="p">[</span><span class="k">on</span><span class="w"> </span><span class="k">update</span><span class="o">|</span><span class="k">delete</span><span class="w"> </span><span class="k">restrict</span><span class="o">|</span><span class="k">cascade</span><span class="o">|</span><span class="k">set</span><span class="w"> </span><span class="k">null</span><span class="p">];</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">ALTER</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="k">table_name</span><span class="w"> </span><span class="k">ADD</span><span class="w"> </span><span class="p">[</span><span class="k">unique</span><span class="p">]</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">index_name</span><span class="w"> </span><span class="p">(</span><span class="n">column1</span><span class="p">[(</span><span class="k">length</span><span class="p">)],</span><span class="n">column2</span><span class="p">[(</span><span class="k">length</span><span class="p">)]);</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">ALTER</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="k">table_name</span><span class="w"> </span><span class="k">DROP</span><span class="w"> </span><span class="p">[</span><span class="k">unique</span><span class="p">]</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">index_name</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">CREATE</span><span class="w"> </span><span class="p">[</span><span class="k">unique</span><span class="p">]</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">index_name</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="k">table</span><span class="p">(</span><span class="k">column</span><span class="p">[(</span><span class="k">length</span><span class="p">)])</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">alter</span><span class="w"> </span><span class="k">table</span><span class="w"> </span><span class="err">表名</span><span class="w"> </span><span class="k">modify</span><span class="w"> </span><span class="err">字段名</span><span class="w"> </span><span class="err">新数据类型</span><span class="w"> </span><span class="p">[</span><span class="k">comment</span><span class="p">]</span><span class="w"> </span><span class="p">[</span><span class="err">约束</span><span class="p">];</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">alter</span><span class="w"> </span><span class="k">table</span><span class="w"> </span><span class="err">表名</span><span class="w"> </span><span class="n">change</span><span class="w"> </span><span class="err">旧字段名</span><span class="w"> </span><span class="err">新字段名</span><span class="w"> </span><span class="err">新数据类型</span><span class="w"> </span><span class="p">[</span><span class="k">comment</span><span class="p">]</span><span class="w"> </span><span class="p">[</span><span class="err">约束</span><span class="p">];</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">alter</span><span class="w"> </span><span class="k">table</span><span class="w"> </span><span class="err">表名</span><span class="w"> </span><span class="k">drop</span><span class="w"> </span><span class="err">字段名</span><span class="p">;</span><span class="w"> 
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="数据类型">数据类型</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="n">TINYINT</span><span class="w">	</span><span class="nb">SMALLINT</span><span class="w">	</span><span class="n">MEDIUMINT</span><span class="w">	</span><span class="nb">INT</span><span class="w">		</span><span class="nb">BIGINT</span><span class="w">	</span><span class="nb">FLOAT</span><span class="w">		</span><span class="n">DOUBLE</span><span class="w">	</span><span class="nb">DECIMAL</span><span class="p">(</span><span class="n">M</span><span class="p">,</span><span class="n">D</span><span class="p">)</span><span class="w">	
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="nb">DATE</span><span class="w">	</span><span class="n">TIME</span><span class="w">	</span><span class="k">YEAR</span><span class="w">	</span><span class="n">DATETIME</span><span class="w">	</span><span class="k">TIMESTAMP</span><span class="w">	
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="nb">CHAR</span><span class="p">(</span><span class="n">n</span><span class="p">)</span><span class="w">	</span><span class="nb">VARCHAR</span><span class="p">(</span><span class="n">n</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">TINYBLOB</span><span class="w">	</span><span class="n">TINYTEXT</span><span class="w">	</span><span class="nb">BLOB</span><span class="w">	</span><span class="nb">TEXT</span><span class="w">	</span><span class="n">MEDIUMBLOB</span><span class="w">	</span><span class="n">MEDIUMTEXT</span><span class="w">	</span><span class="n">LONGBLOB</span><span class="w">	</span><span class="n">LONGTEXT</span><span class="w">	
</span></span></span></code></pre></td></tr></table>
</div>
</div><h2 id="dml">DML</h2>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span><span class="lnt">8
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">insert</span><span class="w"> </span><span class="k">into</span><span class="w"> </span><span class="err">表名</span><span class="p">(</span><span class="err">字段</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">字段</span><span class="mi">2</span><span class="p">)</span><span class="w"> </span><span class="k">values</span><span class="w"> </span><span class="p">(</span><span class="err">值</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">值</span><span class="mi">2</span><span class="p">);</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">insert</span><span class="w"> </span><span class="k">into</span><span class="w"> </span><span class="err">表名</span><span class="w"> </span><span class="k">values</span><span class="w"> </span><span class="p">(</span><span class="err">值</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">值</span><span class="mi">2</span><span class="p">);</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">insert</span><span class="w"> </span><span class="k">into</span><span class="w"> </span><span class="err">表名</span><span class="w"> </span><span class="k">values</span><span class="w"> </span><span class="p">(</span><span class="err">值</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">值</span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="err">值</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">值</span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="err">值</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">值</span><span class="mi">2</span><span class="p">);</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">INSERT</span><span class="w"> </span><span class="k">IGNORE</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="k">table_name</span><span class="w"> </span><span class="p">(</span><span class="n">column1</span><span class="p">,</span><span class="w"> </span><span class="n">column2</span><span class="p">)</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="n">value1</span><span class="p">,</span><span class="w"> </span><span class="n">value2</span><span class="p">);</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">REPLACE</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="k">table_name</span><span class="w"> </span><span class="p">(</span><span class="n">column1</span><span class="p">,</span><span class="w"> </span><span class="n">column2</span><span class="p">)</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="n">value1</span><span class="p">,</span><span class="w"> </span><span class="n">value2</span><span class="p">);</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">INSERT</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">table_name1</span><span class="w"> </span><span class="p">(</span><span class="n">column1</span><span class="p">,</span><span class="w"> </span><span class="n">column2</span><span class="p">)</span><span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="n">column3</span><span class="p">,</span><span class="w"> </span><span class="n">column4</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">table_name2</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">update</span><span class="w"> </span><span class="err">表名</span><span class="w"> </span><span class="k">set</span><span class="w"> </span><span class="err">字段</span><span class="mi">1</span><span class="o">=</span><span class="err">值</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">字段</span><span class="mi">2</span><span class="o">=</span><span class="err">值</span><span class="mi">2</span><span class="w"> </span><span class="p">[</span><span class="k">where</span><span class="w"> </span><span class="err">条件</span><span class="p">];</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">delete</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="err">表名</span><span class="w"> </span><span class="p">[</span><span class="k">where</span><span class="w"> </span><span class="err">条件</span><span class="p">];</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><p>强制忽略外键约束</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">SET</span><span class="w"> </span><span class="n">FOREIGN_KEY_CHECKS</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">0</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- 操作语句
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">SET</span><span class="w"> </span><span class="n">FOREIGN_KEY_CHECKS</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">1</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h2 id="dql">DQL</h2>
<h3 id="查询语句">查询语句</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">select</span><span class="w"> </span><span class="p">[</span><span class="k">distinct</span><span class="p">]</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="err">字段列表</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">from</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="err">表名列表</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">where</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="err">分组前条件列表</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">group</span><span class="w"> </span><span class="k">by</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="err">分组字段列表</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">having</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="err">分组后条件列表</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">order</span><span class="w"> </span><span class="k">by</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="err">排序字段列表</span><span class="w"> </span><span class="p">[</span><span class="k">asc</span><span class="o">|</span><span class="k">desc</span><span class="p">]</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">limit</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="err">页数码</span><span class="p">,</span><span class="w"> </span><span class="err">页大小</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="p">[</span><span class="k">for</span><span class="w"> </span><span class="k">update</span><span class="o">|</span><span class="k">share</span><span class="p">]</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">select</span><span class="w"> </span><span class="err">字段名</span><span class="w"> </span><span class="k">as</span><span class="w"> </span><span class="err">别名</span><span class="w"> </span><span class="n">form</span><span class="w"> </span><span class="err">表名</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="比较运算符">比较运算符</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="p">[</span><span class="k">not</span><span class="p">]</span><span class="w"> </span><span class="k">in</span><span class="p">(</span><span class="err">值</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">值</span><span class="mi">2</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="p">(</span><span class="err">值</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">值</span><span class="mi">2</span><span class="p">)</span><span class="w"> </span><span class="p">[</span><span class="k">not</span><span class="p">]</span><span class="w"> </span><span class="k">in</span><span class="w"> </span><span class="p">(</span><span class="k">select</span><span class="w"> </span><span class="err">值</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">值</span><span class="mi">2</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="p">...)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="p">[</span><span class="k">not</span><span class="p">]</span><span class="w"> </span><span class="k">exists</span><span class="w"> </span><span class="p">(</span><span class="k">select</span><span class="w"> </span><span class="err">值</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">值</span><span class="mi">2</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="p">...)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="err">值</span><span class="mi">1</span><span class="o">&gt;=</span><span class="k">ANY</span><span class="o">|</span><span class="k">ALL</span><span class="w"> </span><span class="p">(</span><span class="k">select</span><span class="w"> </span><span class="err">值</span><span class="mi">1</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="p">...)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">like</span><span class="w"> </span><span class="s1">&#39;_xxx%&#39;</span><span class="w"> </span><span class="k">escape</span><span class="w"> </span><span class="s1">&#39;\&#39;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">is</span><span class="w"> </span><span class="k">null</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="聚合函数">聚合函数</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">max</span><span class="p">(</span><span class="err">字段名</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">min</span><span class="p">(</span><span class="err">字段名</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">avg</span><span class="p">(</span><span class="err">字段名</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">sum</span><span class="p">(</span><span class="err">字段名</span><span class="p">)</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="连表查询">连表查询</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">SELECT</span><span class="w"> </span><span class="n">column1</span><span class="p">,</span><span class="w"> </span><span class="n">column2</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">table1</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">UNION</span><span class="w"> </span><span class="k">ALL</span><span class="o">|</span><span class="k">UNION</span><span class="o">|</span><span class="k">INTERSECT</span><span class="o">|</span><span class="k">EXCEPT</span><span class="w">   </span><span class="n">union去重</span><span class="err">，</span><span class="k">union</span><span class="w"> </span><span class="n">all不去</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="n">column1</span><span class="p">,</span><span class="w"> </span><span class="n">column2</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">table2</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">table1</span><span class="w"> </span><span class="k">JOIN</span><span class="w"> </span><span class="n">table2</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">table1</span><span class="p">.</span><span class="n">id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">table2</span><span class="p">.</span><span class="n">id</span><span class="p">;</span><span class="w"> </span><span class="err">内连接</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">table1</span><span class="w"> </span><span class="k">LEFT</span><span class="w"> </span><span class="k">JOIN</span><span class="w"> </span><span class="n">table2</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">table1</span><span class="p">.</span><span class="n">id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">table2</span><span class="p">.</span><span class="n">id</span><span class="p">;</span><span class="w">  </span><span class="err">左外连接</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">table1</span><span class="w"> </span><span class="k">RIGHT</span><span class="w"> </span><span class="k">JOIN</span><span class="w"> </span><span class="n">table2</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">table1</span><span class="p">.</span><span class="n">id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">table2</span><span class="p">.</span><span class="n">id</span><span class="p">;</span><span class="w"> </span><span class="err">右外连接</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h2 id="dcl">DCL</h2>
<h3 id="用户管理">用户管理</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="n">use</span><span class="w"> </span><span class="n">mysql</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">select</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="k">user</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">create</span><span class="w"> </span><span class="k">user</span><span class="w"> </span><span class="s1">&#39;用户名@主机名&#39;</span><span class="w"> </span><span class="n">identified</span><span class="w"> </span><span class="k">by</span><span class="w"> </span><span class="s1">&#39;密码&#39;</span><span class="w">  </span><span class="k">with</span><span class="w"> </span><span class="k">grant</span><span class="w"> </span><span class="k">option</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">alter</span><span class="w"> </span><span class="k">user</span><span class="w"> </span><span class="s1">&#39;root@%&#39;</span><span class="w"> </span><span class="n">identified</span><span class="w"> </span><span class="k">with</span><span class="w"> </span><span class="n">mysql_native_password</span><span class="w"> </span><span class="k">by</span><span class="w"> </span><span class="s1">&#39;密码&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">drop</span><span class="w"> </span><span class="k">user</span><span class="w"> </span><span class="s1">&#39;用户名@主机名&#39;</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><p>备用用户</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl">sudo cat /etc/mysql/debian.cnf
</span></span><span class="line"><span class="cl"><span class="c1"># debian-sys-maint</span>
</span></span><span class="line"><span class="cl"><span class="c1"># ztv*************</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl">mysql -u debian-sys-maint -p
</span></span></code></pre></td></tr></table>
</div>
</div><div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="n">use</span><span class="w"> </span><span class="n">mysql</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">select</span><span class="w"> </span><span class="k">user</span><span class="p">,</span><span class="n">plugin</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="k">user</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">update</span><span class="w"> </span><span class="k">user</span><span class="w"> </span><span class="k">set</span><span class="w"> </span><span class="n">plugin</span><span class="o">=</span><span class="s1">&#39;mysql_native_password&#39;</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="k">user</span><span class="o">=</span><span class="s1">&#39;root&#39;</span><span class="p">;</span><span class="w"> </span><span class="o">#</span><span class="w"> </span><span class="err">修改其密码格式</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">select</span><span class="w"> </span><span class="k">user</span><span class="p">,</span><span class="n">plugin</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="k">user</span><span class="p">;</span><span class="w"> </span><span class="o">#</span><span class="w"> </span><span class="err">查询其用户</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">flush</span><span class="w"> </span><span class="k">privileges</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">alter</span><span class="w"> </span><span class="k">user</span><span class="w"> </span><span class="s1">&#39;root&#39;</span><span class="o">@</span><span class="s1">&#39;localhost&#39;</span><span class="w"> </span><span class="n">identified</span><span class="w"> </span><span class="k">with</span><span class="w"> </span><span class="n">mysql_native_password</span><span class="w"> </span><span class="k">by</span><span class="w"> </span><span class="s1">&#39;123456&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">flush</span><span class="w"> </span><span class="k">privileges</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="权限列表">权限列表</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span><span class="lnt">28
</span><span class="lnt">29
</span><span class="lnt">30
</span><span class="lnt">31
</span><span class="lnt">32
</span><span class="lnt">33
</span><span class="lnt">34
</span><span class="lnt">35
</span><span class="lnt">36
</span><span class="lnt">37
</span><span class="lnt">38
</span><span class="lnt">39
</span><span class="lnt">40
</span><span class="lnt">41
</span><span class="lnt">42
</span><span class="lnt">43
</span><span class="lnt">44
</span><span class="lnt">45
</span><span class="lnt">46
</span><span class="lnt">47
</span><span class="lnt">48
</span><span class="lnt">49
</span><span class="lnt">50
</span><span class="lnt">51
</span><span class="lnt">52
</span><span class="lnt">53
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-txt" data-lang="txt"><span class="line"><span class="cl">(1)授予数据库权限时，&lt;权限类型&gt;可以指定为以下值：
</span></span><span class="line"><span class="cl">SELECT：表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
</span></span><span class="line"><span class="cl">INSERT：表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
</span></span><span class="line"><span class="cl">DELETE：表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。
</span></span><span class="line"><span class="cl">UPDATE：表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
</span></span><span class="line"><span class="cl">REFERENCES：表示授予用户可以创建指向特定的数据库中的表外键的权限。
</span></span><span class="line"><span class="cl">CREATE：表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
</span></span><span class="line"><span class="cl">ALTER：表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
</span></span><span class="line"><span class="cl">SHOW VIEW：表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
</span></span><span class="line"><span class="cl">CREATE ROUTINE：表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
</span></span><span class="line"><span class="cl">ALTER ROUTINE：表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
</span></span><span class="line"><span class="cl">INDEX：表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
</span></span><span class="line"><span class="cl">DROP：表示授予用户可以删除特定数据库中所有表和视图的权限。
</span></span><span class="line"><span class="cl">CREATE TEMPORARY TABLES：表示授予用户可以在特定数据库中创建临时表的权限。
</span></span><span class="line"><span class="cl">CREATE VIEW：表示授予用户可以在特定数据库中创建新的视图的权限。
</span></span><span class="line"><span class="cl">EXECUTE ROUTINE：表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
</span></span><span class="line"><span class="cl">LOCK TABLES：表示授予用户可以锁定特定数据库的已有数据表的权限。
</span></span><span class="line"><span class="cl">ALL 或 ALL PRIVILEGES：表示以上所有权限。
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl">(2) 授予表权限时，&lt;权限类型&gt;可以指定为以下值：
</span></span><span class="line"><span class="cl">SELECT：授予用户可以使用 SELECT 语句进行访问特定表的权限。
</span></span><span class="line"><span class="cl">INSERT：授予用户可以使用 INSERT 语句向一个特定表中添加数据行的权限。
</span></span><span class="line"><span class="cl">DELETE：授予用户可以使用 DELETE 语句从一个特定表中删除数据行的权限。
</span></span><span class="line"><span class="cl">DROP：授予用户可以删除数据表的权限。
</span></span><span class="line"><span class="cl">UPDATE：授予用户可以使用 UPDATE 语句更新特定数据表的权限。
</span></span><span class="line"><span class="cl">ALTER：授予用户可以使用 ALTER TABLE 语句修改数据表的权限。
</span></span><span class="line"><span class="cl">REFERENCES：授予用户可以创建一个外键来参照特定数据表的权限。
</span></span><span class="line"><span class="cl">CREATE：授予用户可以使用特定的名字创建一个数据表的权限。
</span></span><span class="line"><span class="cl">INDEX：授予用户可以在表上定义索引的权限。
</span></span><span class="line"><span class="cl">ALL 或 ALL PRIVILEGES：所有的权限名。
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl">(3) 授予列权限时，&lt;权限类型&gt;的值只能指定为 SELECT、INSERT 和 UPDATE，同时权限后面需要加上列名列表 column-list。
</span></span><span class="line"><span class="cl">grant insert(id, name) on test.priv_test to sam@‘localhost’;
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl">(4) 系统·权限。
</span></span><span class="line"><span class="cl">CREATE USER：表示授予用户可以创建和删除新用户的权限。
</span></span><span class="line"><span class="cl">SHOW DATABASES：表示授予用户可以使用 SHOW DATABASES 语句查看所有已有的数据库的定义的权限。
</span></span><span class="line"><span class="cl">FILE：表示授予用户可以使用 LOAD DATA INFILE 和 SELECT … INTO OUTFILE 语句访问服务器上的文件的权限。
</span></span><span class="line"><span class="cl">PROCESS：表示授予用户可以使用 SHOW PROCESSLIST 语句查看服务器上的所有进程的权限。
</span></span><span class="line"><span class="cl">RELOAD：表示授予用户可以使用 FLUSH 语句刷新服务器的各种缓存和日志的权限。
</span></span><span class="line"><span class="cl">REPLICATION CLIENT：表示授予用户可以使用 SHOW MASTER STATUS 和 SHOW SLAVE STATUS 语句查看主从复制的状态信息的权限。
</span></span><span class="line"><span class="cl">REPLICATION SLAVE：表示授予用户可以作为从服务器连接到主服务器进行复制的权限。
</span></span><span class="line"><span class="cl">SHUTDOWN：表示授予用户可以使用 SHUTDOWN 语句关闭服务器的权限。
</span></span><span class="line"><span class="cl">SUPER：表示授予用户可以执行一些高级操作，如设置全局变量，杀死其他用户的进程，启用或禁用日志等的权限。
</span></span><span class="line"><span class="cl">USAGE：连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授权）。
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl">WITH GRANT OPTION：表示授予用户可以将自己的权限授权给其他用户的选项。
</span></span><span class="line"><span class="cl">WITH ADMIN OPTION：表示授予用户可以将自己的权限授权给其他用户的选项，包括系统权限。
</span></span><span class="line"><span class="cl">REQUIRE：表示授予用户可以使用安全连接（如SSL或X509）访问服务器的选项。
</span></span><span class="line"><span class="cl">WITH MAX_QUERIES_PER_HOUR count：表示授予用户每小时最多可以执行的查询数。
</span></span><span class="line"><span class="cl">WITH MAX_UPDATES_PER_HOUR count：表示授予用户每小时最多可以执行的更新数。
</span></span><span class="line"><span class="cl">WITH MAX_CONNECTIONS_PER_HOUR count：表示授予用户每小时最多可以建立的连接数。
</span></span><span class="line"><span class="cl">WITH MAX_USER_CONNECTIONS count：表示授予用户同时可以保持的最大连接数。
</span></span></code></pre></td></tr></table>
</div>
</div><h3 id="权限管理">权限管理</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">show</span><span class="w"> </span><span class="n">grants</span><span class="w"> </span><span class="k">for</span><span class="w"> </span><span class="n">root</span><span class="o">@</span><span class="s1">&#39;%&#39;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">show</span><span class="w"> </span><span class="n">grants</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">grant</span><span class="w"> </span><span class="err">权限列表</span><span class="w"> </span><span class="k">on</span><span class="w"> </span><span class="err">数据库名</span><span class="p">.</span><span class="err">表名</span><span class="o">/</span><span class="err">视图名</span><span class="w"> </span><span class="k">to</span><span class="w"> </span><span class="s1">&#39;用户名@主机名&#39;</span><span class="o">|</span><span class="k">public</span><span class="w"> </span><span class="p">[</span><span class="k">with</span><span class="w"> </span><span class="k">grant</span><span class="w"> </span><span class="k">option</span><span class="p">];</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">grant</span><span class="w"> </span><span class="k">EXECUTE</span><span class="w"> </span><span class="k">on</span><span class="w"> </span><span class="k">procedure</span><span class="o">/</span><span class="k">function</span><span class="w"> </span><span class="err">数据库名</span><span class="p">.</span><span class="err">存储过程名</span><span class="w"> </span><span class="k">to</span><span class="w"> </span><span class="s1">&#39;用户名@主机名&#39;</span><span class="o">|</span><span class="k">public</span><span class="w"> </span><span class="p">[</span><span class="k">with</span><span class="w"> </span><span class="k">grant</span><span class="w"> </span><span class="k">option</span><span class="p">];</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">grant</span><span class="w"> </span><span class="k">select</span><span class="p">(</span><span class="err">列名</span><span class="mi">1</span><span class="err">，列名</span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="k">update</span><span class="p">(</span><span class="err">列名</span><span class="mi">1</span><span class="p">)</span><span class="w"> </span><span class="k">on</span><span class="w"> </span><span class="err">数据库名</span><span class="p">.</span><span class="err">表名</span><span class="w"> </span><span class="k">to</span><span class="w"> </span><span class="s1">&#39;user@localhost&#39;</span><span class="o">|</span><span class="k">public</span><span class="w"> </span><span class="p">[</span><span class="k">with</span><span class="w"> </span><span class="k">grant</span><span class="w"> </span><span class="k">option</span><span class="p">];</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">revoke</span><span class="w"> </span><span class="err">权限列表</span><span class="w"> </span><span class="k">on</span><span class="w"> </span><span class="err">数据库名</span><span class="p">.</span><span class="err">表名</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="s1">&#39;用户名@主机名&#39;</span><span class="o">|</span><span class="k">public</span><span class="w"> </span><span class="p">[</span><span class="k">CASCADE</span><span class="o">|</span><span class="k">RESTRICT</span><span class="p">];</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">create</span><span class="w"> </span><span class="k">role</span><span class="w"> </span><span class="err">角色名</span><span class="o">@</span><span class="err">主机名</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">grant</span><span class="w"> </span><span class="err">权限列表</span><span class="w"> </span><span class="k">on</span><span class="w"> </span><span class="err">数据库名</span><span class="p">.</span><span class="err">表名</span><span class="w"> </span><span class="k">to</span><span class="w"> </span><span class="err">用户名</span><span class="o">@</span><span class="err">主机名</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">grant</span><span class="w"> </span><span class="err">角色名</span><span class="o">@</span><span class="err">主机名</span><span class="w"> </span><span class="k">to</span><span class="w"> </span><span class="err">用户名</span><span class="o">@</span><span class="err">主机名</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">revoke</span><span class="w"> </span><span class="err">角色名</span><span class="o">@</span><span class="err">主机名</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="err">用户名</span><span class="o">@</span><span class="err">主机名</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h2 id="流程控制">流程控制</h2>
<h3 id="事务">事务</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">START</span><span class="w"> </span><span class="k">TRANSACTION</span><span class="p">;</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- 一系列的SQL语句，可以包括查询、插入、更新和删除等操作
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">COMMIT</span><span class="o">|</span><span class="k">ROLLBACK</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><p>设置事务隔离级别</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">set</span><span class="w"> </span><span class="k">global</span><span class="o">|</span><span class="k">session</span><span class="w"> </span><span class="k">transaction</span><span class="w"> </span><span class="k">isolation</span><span class="w"> </span><span class="k">level</span><span class="w"> </span><span class="p">[</span><span class="k">READ</span><span class="w"> </span><span class="k">UNCOMMITTED</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">READ</span><span class="w"> </span><span class="k">COMMITTED</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">REPEATABLE</span><span class="w"> </span><span class="k">READ</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">SERIALIZABLE</span><span class="p">]</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="视图">视图</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">CREATE</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="n">view_name</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="n">column1</span><span class="p">,</span><span class="w"> </span><span class="n">column2</span><span class="p">,</span><span class="w"> </span><span class="p">...</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="k">table_name</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">condition</span><span class="w"> </span><span class="k">WITH</span><span class="w"> </span><span class="k">CASCADED</span><span class="o">|</span><span class="k">LOCAL</span><span class="w"> </span><span class="k">CHECK</span><span class="w"> </span><span class="k">OPTION</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">view_name</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">DROP</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="n">view_name</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="变量">变量</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">SET</span><span class="w"> </span><span class="o">@</span><span class="n">var_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">value</span><span class="p">;</span><span class="w"> </span><span class="err">用户自定义变量的作用域只在当前的</span><span class="n">MySQL连接中有效</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">SET</span><span class="w"> </span><span class="o">@@</span><span class="k">global</span><span class="p">.</span><span class="n">var_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">value</span><span class="p">;</span><span class="w"> </span><span class="err">全局变量的作用域在整个</span><span class="w"> </span><span class="n">MySQL</span><span class="w"> </span><span class="err">实例中有效</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">DECLARE</span><span class="w"> </span><span class="n">var_name</span><span class="w"> </span><span class="n">data_type</span><span class="w"> </span><span class="k">DEFAULT</span><span class="w"> </span><span class="n">value</span><span class="p">;</span><span class="w"> </span><span class="err">局部变量的作用域只在存储过程或函数中有效。</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">select</span><span class="w"> </span><span class="o">@</span><span class="n">var_name</span><span class="p">;</span><span class="w"> </span><span class="err">查看变量</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">select</span><span class="w"> </span><span class="p">...</span><span class="w"> </span><span class="k">into</span><span class="w"> </span><span class="err">变量</span><span class="mi">1</span><span class="p">,</span><span class="err">变量</span><span class="mi">2</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="临时表">临时表</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">CREATE</span><span class="w"> </span><span class="k">TEMPORARY</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="err">表名</span><span class="p">(...)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">CREATE</span><span class="w"> </span><span class="k">TEMPORARY</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="err">表名</span><span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="p">...</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">drop</span><span class="w"> </span><span class="k">table</span><span class="w"> </span><span class="err">表名</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="存储过程">存储过程</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span><span class="lnt">28
</span><span class="lnt">29
</span><span class="lnt">30
</span><span class="lnt">31
</span><span class="lnt">32
</span><span class="lnt">33
</span><span class="lnt">34
</span><span class="lnt">35
</span><span class="lnt">36
</span><span class="lnt">37
</span><span class="lnt">38
</span><span class="lnt">39
</span><span class="lnt">40
</span><span class="lnt">41
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">CREATE</span><span class="w"> </span><span class="k">PROCEDURE</span><span class="w"> </span><span class="n">procedure_name</span><span class="w"> </span><span class="p">([</span><span class="k">IN</span><span class="o">|</span><span class="k">OUT</span><span class="o">|</span><span class="k">INOUT</span><span class="p">]</span><span class="w"> </span><span class="err">变量名</span><span class="w"> </span><span class="err">变量类型</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">BEGIN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="c1">-- 存储过程主体
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">	</span><span class="p">[</span><span class="n">leave</span><span class="w"> </span><span class="n">procedure_name</span><span class="p">;]</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">END</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">CALL</span><span class="w"> </span><span class="n">procedure_name</span><span class="p">(</span><span class="err">参数</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">参数</span><span class="mi">2</span><span class="p">);</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">IF</span><span class="w"> </span><span class="n">condition</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">statements</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">ELSEIF</span><span class="w"> </span><span class="n">condition</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">statements</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">ELSE</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">statements</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">END</span><span class="w"> </span><span class="k">IF</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">CASE</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">WHEN</span><span class="w"> </span><span class="n">condition</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="n">statement_list</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="p">[</span><span class="k">WHEN</span><span class="w"> </span><span class="n">condition</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="n">statement_list</span><span class="p">]</span><span class="w"> </span><span class="p">...</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="p">[</span><span class="k">ELSE</span><span class="w"> </span><span class="n">statement_list</span><span class="p">]</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">END</span><span class="w"> </span><span class="k">CASE</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">WHILE</span><span class="w"> </span><span class="err">条件语句</span><span class="w"> </span><span class="k">DO</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="k">CONTINUE</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="n">BREAK</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="c1">-- 循环体
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">END</span><span class="w"> </span><span class="n">WHILE</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">REPEAT</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="k">CONTINUE</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="n">BREAK</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">   </span><span class="c1">-- 循环体
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">UNTIL</span><span class="w"> </span><span class="err">条件语句</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">END</span><span class="w"> </span><span class="n">REPEAT</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">my_loop</span><span class="p">:</span><span class="w"> </span><span class="n">LOOP</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="c1">-- 循环体
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">	</span><span class="k">iterate</span><span class="w"> </span><span class="n">my_loop</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="n">leave</span><span class="w"> </span><span class="n">my_loop</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">END</span><span class="w"> </span><span class="n">LOOP</span><span class="w"> </span><span class="n">my_loop</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">call</span><span class="w"> </span><span class="err">存储过程名称</span><span class="p">(</span><span class="err">存储过程的参数列表</span><span class="p">)</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="异常处理">异常处理</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="n">SIGNAL</span><span class="w"> </span><span class="p">[</span><span class="k">SQLSTATE</span><span class="w"> </span><span class="s1">&#39;45000&#39;</span><span class="p">]</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="k">MESSAGE_TEXT</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;message&#39;</span><span class="p">;</span><span class="w"> </span><span class="c1">--抛出异常
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">DECLARE</span><span class="w"> </span><span class="n">handler_name</span><span class="w"> </span><span class="n">EXIT</span><span class="o">|</span><span class="k">CONTINUE</span><span class="w"> </span><span class="k">HANDLER</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">SQLEXCEPTION</span><span class="o">|</span><span class="k">NOT</span><span class="w"> </span><span class="k">FOUND</span><span class="o">|</span><span class="n">SQLWARNING</span><span class="o">|</span><span class="k">SQLSTATE</span><span class="w"> </span><span class="s1">&#39;45000&#39;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">BEGIN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="c1">--异常处理语句
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">	</span><span class="p">[</span><span class="k">ROLLBACK</span><span class="p">;]</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">END</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="游标">游标</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="c1">--游标例子：
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">DELIMITER</span><span class="w"> </span><span class="o">//</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">CREATE</span><span class="w"> </span><span class="k">PROCEDURE</span><span class="w"> </span><span class="n">list_employees</span><span class="p">()</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">BEGIN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">DECLARE</span><span class="w"> </span><span class="n">done</span><span class="w"> </span><span class="nb">INT</span><span class="w"> </span><span class="k">DEFAULT</span><span class="w"> </span><span class="k">FALSE</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">DECLARE</span><span class="w"> </span><span class="n">emp_name</span><span class="w"> </span><span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">255</span><span class="p">);</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">DECLARE</span><span class="w"> </span><span class="n">emp_salary</span><span class="w"> </span><span class="nb">FLOAT</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">DECLARE</span><span class="w"> </span><span class="n">cur</span><span class="w"> </span><span class="k">CURSOR</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">SELECT</span><span class="w"> </span><span class="n">name</span><span class="p">,</span><span class="w"> </span><span class="n">salary</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">employees</span><span class="p">;</span><span class="w"> </span><span class="c1">--声明游标
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">    </span><span class="k">DECLARE</span><span class="w"> </span><span class="k">CONTINUE</span><span class="w"> </span><span class="k">HANDLER</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">FOUND</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">done</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">TRUE</span><span class="p">;</span><span class="w"> </span><span class="c1">--处理异常
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">    </span><span class="k">OPEN</span><span class="w"> </span><span class="n">cur</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">read_loop</span><span class="p">:</span><span class="w"> </span><span class="n">LOOP</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">        </span><span class="k">FETCH</span><span class="w"> </span><span class="n">cur</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">emp_name</span><span class="p">,</span><span class="w"> </span><span class="n">emp_salary</span><span class="p">;</span><span class="w"> </span><span class="c1">--fetch游标next
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">        </span><span class="k">IF</span><span class="w"> </span><span class="n">done</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">            </span><span class="n">LEAVE</span><span class="w"> </span><span class="n">read_loop</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">        </span><span class="k">END</span><span class="w"> </span><span class="k">IF</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">        </span><span class="k">SELECT</span><span class="w"> </span><span class="n">CONCAT</span><span class="p">(</span><span class="n">emp_name</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39; earns &#39;</span><span class="p">,</span><span class="w"> </span><span class="n">emp_salary</span><span class="p">);</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">END</span><span class="w"> </span><span class="n">LOOP</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">CLOSE</span><span class="w"> </span><span class="n">cur</span><span class="p">;</span><span class="w">  </span><span class="c1">--关闭游标
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">END</span><span class="o">//</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">DELIMITER</span><span class="w"> </span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="存储函数">存储函数</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">CREATE</span><span class="w"> </span><span class="k">FUNCTION</span><span class="w"> </span><span class="n">function_name</span><span class="w"> </span><span class="p">(</span><span class="err">参数</span><span class="w"> </span><span class="err">参数类型</span><span class="p">,</span><span class="w"> </span><span class="err">参数</span><span class="w"> </span><span class="err">参数类型</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="k">RETURNS</span><span class="w"> </span><span class="err">数据类型</span><span class="w"> </span><span class="p">[</span><span class="k">no</span><span class="w"> </span><span class="k">sql</span><span class="p">,</span><span class="w"> </span><span class="k">read</span><span class="w"> </span><span class="k">sql</span><span class="w"> </span><span class="k">data</span><span class="p">]</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="k">BEGIN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    		</span><span class="c1">-- Function body
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">		</span><span class="k">RETURN</span><span class="w"> </span><span class="k">result</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="k">END</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="err">函数名</span><span class="p">(</span><span class="err">参数</span><span class="p">);</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="触发器">触发器</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">CREATE</span><span class="w"> </span><span class="k">TRIGGER</span><span class="w"> </span><span class="err">触发器名</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">BEFORE</span><span class="o">|</span><span class="k">AFTER</span><span class="w"> </span><span class="k">INSERT</span><span class="o">|</span><span class="k">UPDATE</span><span class="o">|</span><span class="k">DELETE</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="err">表名</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">FOR</span><span class="w"> </span><span class="k">EACH</span><span class="w"> </span><span class="k">ROW</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">BEGIN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">	</span><span class="c1">--NEW表表示修改后的表
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">	</span><span class="c1">--OLD表示修改前的表
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">	</span><span class="p">[</span><span class="k">ROLLBACK</span><span class="p">;]</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">END</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">select</span><span class="w"> </span><span class="err">存储函数名称</span><span class="w"> </span><span class="p">(</span><span class="err">存储函数参数列表</span><span class="p">);</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="mysql函数">MYSQL函数</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="n">concat</span><span class="p">(</span><span class="err">字符串</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="err">字符串</span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="p">...)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">lower</span><span class="p">(</span><span class="n">str</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">upper</span><span class="p">(</span><span class="n">str</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">lpad</span><span class="p">(</span><span class="n">str</span><span class="p">,</span><span class="w"> </span><span class="n">n</span><span class="p">,</span><span class="w"> </span><span class="k">pad</span><span class="p">)</span><span class="w"> </span><span class="c1">--用字符串pad对str左填充达到n的长度
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="n">rpad</span><span class="p">(</span><span class="n">str</span><span class="p">,</span><span class="w"> </span><span class="n">n</span><span class="p">,</span><span class="w"> </span><span class="k">pad</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">trim</span><span class="p">(</span><span class="n">str</span><span class="p">)</span><span class="w"> </span><span class="c1">--去除头尾空格
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">substring</span><span class="p">(</span><span class="n">str</span><span class="p">,</span><span class="w"> </span><span class="k">start</span><span class="p">,</span><span class="w"> </span><span class="n">len</span><span class="p">)</span><span class="w"> </span><span class="c1">--索引值从1开始不是0
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">ceil</span><span class="p">(</span><span class="n">x</span><span class="p">)</span><span class="w"> </span><span class="c1">--向上取整
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="n">floor</span><span class="p">(</span><span class="n">x</span><span class="p">)</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">mod</span><span class="p">(</span><span class="n">x</span><span class="p">,</span><span class="n">y</span><span class="p">)</span><span class="w"> </span><span class="c1">--模
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="n">rand</span><span class="p">()</span><span class="w">  </span><span class="c1">--随机数(0,1)
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="n">round</span><span class="p">(</span><span class="n">x</span><span class="p">,</span><span class="w"> </span><span class="n">y</span><span class="p">)</span><span class="w"> </span><span class="c1">--对x四舍五入保留y位小数
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">curdate</span><span class="p">()</span><span class="w"> </span><span class="c1">--当前date
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="n">curtime</span><span class="p">()</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">now</span><span class="p">()</span><span class="w"> </span><span class="c1">--当前datetime
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">day</span><span class="o">|</span><span class="k">month</span><span class="o">|</span><span class="k">year</span><span class="p">(</span><span class="nb">date</span><span class="p">)</span><span class="w"> </span><span class="c1">--获取年份
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">day</span><span class="p">(</span><span class="nb">date</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">date_add</span><span class="p">(</span><span class="nb">date</span><span class="p">,</span><span class="w"> </span><span class="nb">interval</span><span class="w"> </span><span class="n">n</span><span class="w"> </span><span class="k">year</span><span class="o">|</span><span class="k">month</span><span class="o">|</span><span class="k">day</span><span class="p">)</span><span class="w"> </span><span class="c1">--当前日期加n年|月|日
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="n">datediff</span><span class="p">(</span><span class="n">date1</span><span class="p">,</span><span class="w"> </span><span class="n">date2</span><span class="p">)</span><span class="w"> </span><span class="c1">--相差天数d1-d2
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">if</span><span class="p">(</span><span class="n">value</span><span class="p">,</span><span class="w"> </span><span class="n">t</span><span class="p">,</span><span class="w"> </span><span class="n">f</span><span class="p">)</span><span class="w"> </span><span class="c1">--value位true则返回t否则返回f
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="n">ifnull</span><span class="p">(</span><span class="n">t</span><span class="p">,</span><span class="w"> </span><span class="n">f</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">case</span><span class="w"> </span><span class="k">when</span><span class="w"> </span><span class="err">表达式</span><span class="w"> </span><span class="k">then</span><span class="w"> </span><span class="err">值</span><span class="w"> </span><span class="k">when</span><span class="w"> </span><span class="err">表达式</span><span class="w"> </span><span class="k">then</span><span class="w"> </span><span class="err">值</span><span class="w"> </span><span class="k">else</span><span class="w"> </span><span class="err">值</span><span class="w"> </span><span class="k">end</span><span class="p">;</span><span class="w"> 
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="事件">事件</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">SET</span><span class="w"> </span><span class="k">GLOBAL</span><span class="w"> </span><span class="n">event_scheduler</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">ON</span><span class="p">;</span><span class="w"> </span><span class="c1">--开启事件调度器
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">CREATE</span><span class="w"> </span><span class="n">EVENT</span><span class="w"> </span><span class="p">[</span><span class="k">IF</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">EXISTS</span><span class="p">]</span><span class="w"> </span><span class="n">event_name</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">ON</span><span class="w"> </span><span class="n">SCHEDULE</span><span class="w"> </span><span class="n">schedule</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="p">[</span><span class="k">ON</span><span class="w"> </span><span class="k">COMPLETION</span><span class="w"> </span><span class="p">[</span><span class="k">NOT</span><span class="p">]</span><span class="w"> </span><span class="k">PRESERVE</span><span class="p">]</span><span class="w">   </span><span class="c1">--是否循环执行
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="p">[</span><span class="n">ENABLE</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">DISABLE</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">DISABLE</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">SLAVE</span><span class="p">]</span><span class="w">  </span><span class="c1">--事件是否启动
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="k">DO</span><span class="w"> </span><span class="n">event_body</span><span class="p">;</span><span class="w">  
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">schedule</span><span class="p">:</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">AT</span><span class="w"> </span><span class="k">timestamp</span><span class="w"> </span><span class="p">[</span><span class="o">+</span><span class="w"> </span><span class="nb">INTERVAL</span><span class="w"> </span><span class="nb">interval</span><span class="p">]</span><span class="o">|</span><span class="w"> </span><span class="k">EVERY</span><span class="w"> </span><span class="nb">interval</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="p">[</span><span class="n">STARTS</span><span class="w"> </span><span class="k">timestamp</span><span class="w"> </span><span class="p">[</span><span class="o">+</span><span class="w"> </span><span class="nb">INTERVAL</span><span class="w"> </span><span class="nb">interval</span><span class="p">]]</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="p">[</span><span class="n">ENDS</span><span class="w"> </span><span class="k">timestamp</span><span class="w"> </span><span class="p">[</span><span class="o">+</span><span class="w"> </span><span class="nb">INTERVAL</span><span class="w"> </span><span class="nb">interval</span><span class="p">]]</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- INTERVAL中包含的时间单位如下:
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="err">{</span><span class="k">YEAR</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">QUARTER</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">MONTH</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">DAY</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">HOUR</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">MINUTE</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">WEEK</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">SECOND</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">YEAR_MONTH</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">DAY_HOUR</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">DAY_MINUTE</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">DAY_SECOND</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">HOUR_MINUTE</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">HOUR_SECOND</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">MINUTE_SECOND</span><span class="err">}</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">show</span><span class="w"> </span><span class="n">events</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">ALTER</span><span class="w"> </span><span class="n">EVENT</span><span class="w"> </span><span class="n">event_name</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">SCHEDULE</span><span class="w"> </span><span class="k">EVERY</span><span class="w"> </span><span class="mi">2</span><span class="w"> </span><span class="k">MINUTE</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">ALTER</span><span class="w"> </span><span class="n">EVENT</span><span class="w"> </span><span class="n">event_name</span><span class="w"> </span><span class="k">DO</span><span class="w"> </span><span class="k">insert</span><span class="w"> </span><span class="k">into</span><span class="w"> </span><span class="n">events_list</span><span class="w"> </span><span class="k">values</span><span class="p">(</span><span class="s1">&#39;event&#39;</span><span class="p">,</span><span class="w"> </span><span class="n">now</span><span class="p">());</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">ALTER</span><span class="w"> </span><span class="n">EVENT</span><span class="w"> </span><span class="n">event_name</span><span class="w"> </span><span class="n">DISABLE</span><span class="o">|</span><span class="n">ENABLE</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">ALTER</span><span class="w"> </span><span class="n">EVENT</span><span class="w"> </span><span class="n">event_name</span><span class="w"> </span><span class="k">RENAME</span><span class="w"> </span><span class="k">TO</span><span class="w"> </span><span class="n">test_event</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h2 id="程序举例">程序举例</h2>
<h3 id="银行转账存储过程">银行转账存储过程</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span><span class="lnt">28
</span><span class="lnt">29
</span><span class="lnt">30
</span><span class="lnt">31
</span><span class="lnt">32
</span><span class="lnt">33
</span><span class="lnt">34
</span><span class="lnt">35
</span><span class="lnt">36
</span><span class="lnt">37
</span><span class="lnt">38
</span><span class="lnt">39
</span><span class="lnt">40
</span><span class="lnt">41
</span><span class="lnt">42
</span><span class="lnt">43
</span><span class="lnt">44
</span><span class="lnt">45
</span><span class="lnt">46
</span><span class="lnt">47
</span><span class="lnt">48
</span><span class="lnt">49
</span><span class="lnt">50
</span><span class="lnt">51
</span><span class="lnt">52
</span><span class="lnt">53
</span><span class="lnt">54
</span><span class="lnt">55
</span><span class="lnt">56
</span><span class="lnt">57
</span><span class="lnt">58
</span><span class="lnt">59
</span><span class="lnt">60
</span><span class="lnt">61
</span><span class="lnt">62
</span><span class="lnt">63
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">CREATE</span><span class="w"> </span><span class="k">PROCEDURE</span><span class="w"> </span><span class="n">transfer</span><span class="p">(</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">IN</span><span class="w"> </span><span class="n">from_account</span><span class="w"> </span><span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">IN</span><span class="w"> </span><span class="n">to_account</span><span class="w"> </span><span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">IN</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">10</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">BEGIN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">DECLARE</span><span class="w"> </span><span class="n">from_balance</span><span class="w"> </span><span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">10</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">);</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">DECLARE</span><span class="w"> </span><span class="n">to_balance</span><span class="w"> </span><span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">10</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">);</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="c1">-- 检查参数是否合法
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="k">IF</span><span class="w"> </span><span class="n">from_account</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">to_account</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">SIGNAL</span><span class="w"> </span><span class="k">SQLSTATE</span><span class="w"> </span><span class="s1">&#39;45000&#39;</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="k">MESSAGE_TEXT</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;Error: same account&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">LEAVE</span><span class="w"> </span><span class="n">transfer</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">END</span><span class="w"> </span><span class="k">IF</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">IF</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="o">&lt;=</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">SIGNAL</span><span class="w"> </span><span class="k">SQLSTATE</span><span class="w"> </span><span class="s1">&#39;45000&#39;</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="k">MESSAGE_TEXT</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;Error: invalid amount&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">LEAVE</span><span class="w"> </span><span class="n">transfer</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">END</span><span class="w"> </span><span class="k">IF</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">DECLARE</span><span class="w"> </span><span class="n">EXIT</span><span class="w"> </span><span class="k">HANDLER</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">SQLEXCEPTION</span><span class="w"> </span><span class="k">ROLLBACK</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">START</span><span class="w"> </span><span class="k">TRANSACTION</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="c1">-- 查询转出账户是否存在
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="k">SELECT</span><span class="w"> </span><span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="o">@</span><span class="k">count</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">from_account</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="c1">-- 如果不存在，抛出异常
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="k">IF</span><span class="w"> </span><span class="o">@</span><span class="k">count</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">SIGNAL</span><span class="w"> </span><span class="k">SQLSTATE</span><span class="w"> </span><span class="s1">&#39;45000&#39;</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="k">MESSAGE_TEXT</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;Error: account not found&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">LEAVE</span><span class="w"> </span><span class="n">transfer</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">END</span><span class="w"> </span><span class="k">IF</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="c1">-- 查询转出账户的余额
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="k">SELECT</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">from_balance</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">from_account</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">UPDATE</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="c1">-- 检查余额是否足够转账
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="k">IF</span><span class="w"> </span><span class="n">from_balance</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">SIGNAL</span><span class="w"> </span><span class="k">SQLSTATE</span><span class="w"> </span><span class="s1">&#39;45000&#39;</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="k">MESSAGE_TEXT</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;Error: insufficient balance&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">LEAVE</span><span class="w"> </span><span class="n">transfer</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">END</span><span class="w"> </span><span class="k">IF</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="c1">-- 更新转出账户的余额
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="k">UPDATE</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="o">-</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">from_account</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="c1">-- 查询转入账户是否存在
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="k">SELECT</span><span class="w"> </span><span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="o">@</span><span class="k">count</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">to_account</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="c1">-- 如果不存在，抛出异常
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="k">IF</span><span class="w"> </span><span class="o">@</span><span class="k">count</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">SIGNAL</span><span class="w"> </span><span class="k">SQLSTATE</span><span class="w"> </span><span class="s1">&#39;45000&#39;</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="k">MESSAGE_TEXT</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;Error: account not found&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">LEAVE</span><span class="w"> </span><span class="n">transfer</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">END</span><span class="w"> </span><span class="k">IF</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="c1">-- 查询转入账户的余额
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="k">SELECT</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">to_balance</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">to_account</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">UPDATE</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="c1">-- 更新转入账户的余额
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="k">UPDATE</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="o">+</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">to_account</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">COMMIT</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">SELECT</span><span class="w"> </span><span class="s1">&#39;Success&#39;</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="k">result</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">END</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><p>避免死锁</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span><span class="lnt">28
</span><span class="lnt">29
</span><span class="lnt">30
</span><span class="lnt">31
</span><span class="lnt">32
</span><span class="lnt">33
</span><span class="lnt">34
</span><span class="lnt">35
</span><span class="lnt">36
</span><span class="lnt">37
</span><span class="lnt">38
</span><span class="lnt">39
</span><span class="lnt">40
</span><span class="lnt">41
</span><span class="lnt">42
</span><span class="lnt">43
</span><span class="lnt">44
</span><span class="lnt">45
</span><span class="lnt">46
</span><span class="lnt">47
</span><span class="lnt">48
</span><span class="lnt">49
</span><span class="lnt">50
</span><span class="lnt">51
</span><span class="lnt">52
</span><span class="lnt">53
</span><span class="lnt">54
</span><span class="lnt">55
</span><span class="lnt">56
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">CREATE</span><span class="w"> </span><span class="k">PROCEDURE</span><span class="w"> </span><span class="n">transfer</span><span class="p">(</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">IN</span><span class="w"> </span><span class="n">from_account</span><span class="w"> </span><span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">IN</span><span class="w"> </span><span class="n">to_account</span><span class="w"> </span><span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">IN</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">10</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">BEGIN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">DECLARE</span><span class="w"> </span><span class="n">from_balance</span><span class="w"> </span><span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">10</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">);</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">DECLARE</span><span class="w"> </span><span class="n">to_balance</span><span class="w"> </span><span class="nb">DECIMAL</span><span class="p">(</span><span class="mi">10</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">);</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="c1">-- 检查参数是否合法
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="k">IF</span><span class="w"> </span><span class="n">from_account</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">to_account</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">SIGNAL</span><span class="w"> </span><span class="k">SQLSTATE</span><span class="w"> </span><span class="s1">&#39;45000&#39;</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="k">MESSAGE_TEXT</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;Error: same account&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">LEAVE</span><span class="w"> </span><span class="n">transfer</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">END</span><span class="w"> </span><span class="k">IF</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">IF</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="o">&lt;=</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">SIGNAL</span><span class="w"> </span><span class="k">SQLSTATE</span><span class="w"> </span><span class="s1">&#39;45000&#39;</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="k">MESSAGE_TEXT</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;Error: invalid amount&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="n">LEAVE</span><span class="w"> </span><span class="n">transfer</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">END</span><span class="w"> </span><span class="k">IF</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">DECLARE</span><span class="w"> </span><span class="n">EXIT</span><span class="w"> </span><span class="k">HANDLER</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">SQLEXCEPTION</span><span class="w"> </span><span class="k">ROLLBACK</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">START</span><span class="w"> </span><span class="k">TRANSACTION</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="c1">-- 按照字母顺序访问账户
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">  </span><span class="k">IF</span><span class="w"> </span><span class="n">from_account</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="n">to_account</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="c1">-- 查询和更新转出账户
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">    </span><span class="k">SELECT</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">from_balance</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">from_account</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">UPDATE</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">IF</span><span class="w"> </span><span class="n">from_balance</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">      </span><span class="n">SIGNAL</span><span class="w"> </span><span class="k">SQLSTATE</span><span class="w"> </span><span class="s1">&#39;45000&#39;</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="k">MESSAGE_TEXT</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;Error: insufficient balance&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">      </span><span class="k">ROLLBACK</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">      </span><span class="n">LEAVE</span><span class="w"> </span><span class="n">transfer</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">END</span><span class="w"> </span><span class="k">IF</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">UPDATE</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="o">-</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">from_account</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="c1">-- 查询和更新转入账户
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">    </span><span class="k">SELECT</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">to_balance</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">to_account</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">UPDATE</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">UPDATE</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="o">+</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">to_account</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">ELSE</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="c1">-- 查询和更新转入账户
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">    </span><span class="k">SELECT</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">to_balance</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">to_account</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">UPDATE</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">UPDATE</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="o">+</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">to_account</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="c1">-- 查询和更新转出账户
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="w">    </span><span class="k">SELECT</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">from_balance</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">from_account</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">UPDATE</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">IF</span><span class="w"> </span><span class="n">from_balance</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">      </span><span class="n">SIGNAL</span><span class="w"> </span><span class="k">SQLSTATE</span><span class="w"> </span><span class="s1">&#39;45000&#39;</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="k">MESSAGE_TEXT</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;Error: insufficient balance&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">      </span><span class="k">ROLLBACK</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">      </span><span class="n">LEAVE</span><span class="w"> </span><span class="n">transfer</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">END</span><span class="w"> </span><span class="k">IF</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">UPDATE</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">balance</span><span class="w"> </span><span class="o">-</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">account_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">from_account</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">END</span><span class="w"> </span><span class="k">IF</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">COMMIT</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">SELECT</span><span class="w"> </span><span class="s1">&#39;Success&#39;</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="k">result</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">END</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="加法器存储函数">加法器存储函数</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span><span class="lnt">8
</span><span class="lnt">9
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">CREATE</span><span class="w"> </span><span class="k">FUNCTION</span><span class="w"> </span><span class="n">calculate_total</span><span class="p">(</span><span class="n">num1</span><span class="w"> </span><span class="nb">INT</span><span class="p">,</span><span class="w"> </span><span class="n">num2</span><span class="w"> </span><span class="nb">INT</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">RETURNS</span><span class="w"> </span><span class="nb">INT</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">BEGIN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">DECLARE</span><span class="w"> </span><span class="n">total</span><span class="w"> </span><span class="nb">INT</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">SET</span><span class="w"> </span><span class="n">total</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">num1</span><span class="w"> </span><span class="o">+</span><span class="w"> </span><span class="n">num2</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">    </span><span class="k">RETURN</span><span class="w"> </span><span class="n">total</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">END</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">SELECT</span><span class="w"> </span><span class="n">calculate_total</span><span class="p">(</span><span class="mi">5</span><span class="p">,</span><span class="w"> </span><span class="mi">10</span><span class="p">);</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="清理垃圾事件">清理垃圾事件</h3>
<p>每天凌晨1点删除orders表中30天前的数据</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">CREATE</span><span class="w"> </span><span class="n">EVENT</span><span class="w"> </span><span class="k">IF</span><span class="w"> </span><span class="k">NOT</span><span class="w"> </span><span class="k">EXISTS</span><span class="w"> </span><span class="n">clean_old_orders</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">ON</span><span class="w"> </span><span class="n">SCHEDULE</span><span class="w"> </span><span class="k">EVERY</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">DAY</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">STARTS</span><span class="w"> </span><span class="p">(</span><span class="k">TIMESTAMP</span><span class="p">(</span><span class="k">CURRENT_DATE</span><span class="p">)</span><span class="w"> </span><span class="o">+</span><span class="w"> </span><span class="nb">INTERVAL</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">DAY</span><span class="w"> </span><span class="o">+</span><span class="w"> </span><span class="nb">INTERVAL</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="n">HOUR</span><span class="p">)</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">DO</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">  </span><span class="k">DELETE</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">orders</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">order_date</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="n">DATE_SUB</span><span class="p">(</span><span class="n">NOW</span><span class="p">(),</span><span class="w"> </span><span class="nb">INTERVAL</span><span class="w"> </span><span class="mi">30</span><span class="w"> </span><span class="k">DAY</span><span class="p">);</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="删除保护触发器">删除保护触发器</h3>
<p>如果这是最后一个管理员，那么触发器将回滚事务，防止删除操作。</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">CREATE</span><span class="w"> </span><span class="k">TRIGGER</span><span class="w"> </span><span class="n">prevent_last_admin_deletion</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">BEFORE</span><span class="w"> </span><span class="k">DELETE</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">ON</span><span class="w"> </span><span class="k">admin</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">FOR</span><span class="w"> </span><span class="k">EACH</span><span class="w"> </span><span class="k">ROW</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">BEGIN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">   </span><span class="k">DECLARE</span><span class="w"> </span><span class="n">admin_count</span><span class="w"> </span><span class="nb">INT</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">   </span><span class="k">SELECT</span><span class="w"> </span><span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">admin_count</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="k">admin</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">   </span><span class="k">IF</span><span class="w"> </span><span class="n">admin_count</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">THEN</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">      </span><span class="n">SIGNAL</span><span class="w"> </span><span class="k">SQLSTATE</span><span class="w"> </span><span class="s1">&#39;45000&#39;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">         </span><span class="k">SET</span><span class="w"> </span><span class="k">MESSAGE_TEXT</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;Cannot delete the last admin.&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">   </span><span class="k">END</span><span class="w"> </span><span class="k">IF</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">END</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h2 id="锁">锁</h2>
<h3 id="全局锁">全局锁</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="n">FLUSH</span><span class="w"> </span><span class="n">TABLES</span><span class="w"> </span><span class="k">WITH</span><span class="w"> </span><span class="k">WRITE</span><span class="o">/</span><span class="k">READ</span><span class="w"> </span><span class="k">LOCK</span><span class="p">;</span><span class="w"> </span><span class="c1">--全局锁（锁数据库）：
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="n">UNLOCK</span><span class="w"> </span><span class="n">TABLES</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="表锁">表锁</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="c1">-- DROP/ALTER/TRUNCATE TABLE过程中自动加表锁
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">LOCK</span><span class="w"> </span><span class="n">TABLES</span><span class="w"> </span><span class="k">table_name</span><span class="w"> </span><span class="k">WRITE</span><span class="o">/</span><span class="k">READ</span><span class="p">;</span><span class="w"> </span><span class="c1">--表锁
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="n">UNLOCK</span><span class="w"> </span><span class="n">TABLES</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="行级锁">行级锁</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="c1">-- INSERT/UPDATE/DELETE会自动加排他锁（FOR UPDATE）
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="p">...</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="k">UPDATE</span><span class="o">/</span><span class="k">LOCK</span><span class="w"> </span><span class="k">IN</span><span class="w"> </span><span class="k">SHARE</span><span class="w"> </span><span class="k">MODE</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- InnoDB 在行锁很多时有可能自动升级表锁
</span></span></span></code></pre></td></tr></table>
</div>
</div><h2 id="数据库备份">数据库备份</h2>
<h3 id="sql模式">sql模式</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl">mysqldump -u username -p dbname <span class="o">[</span>tbname ... <span class="o">]</span> <span class="o">[</span>--all-databases<span class="o">]</span> <span class="o">[</span>-d 只备份结构<span class="o">]</span>&gt; filename.sql
</span></span><span class="line"><span class="cl">mysqldump --single-transaction -u username -p  <span class="c1"># 保证导出数据一致性</span>
</span></span></code></pre></td></tr></table>
</div>
</div><h3 id="txt模式">txt模式</h3>
<p>修改my.ini或/etc/my.cnf:  secure-file-priv=</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">runoob_tbl</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">INTO</span><span class="w"> </span><span class="n">OUTFILE</span><span class="w"> </span><span class="s1">&#39;/tmp/runoob.txt&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">LOAD</span><span class="w"> </span><span class="k">DATA</span><span class="w"> </span><span class="k">LOCAL</span><span class="w"> </span><span class="n">INFILE</span><span class="w"> </span><span class="s1">&#39;dump.txt&#39;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">INTO</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">mytbl</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="csv模式">csv模式</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">runoob_tbl</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="k">INTO</span><span class="w"> </span><span class="n">OUTFILE</span><span class="w"> </span><span class="s1">&#39;/tmp/runoob.txt&#39;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">FIELDS</span><span class="w"> </span><span class="n">TERMINATED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;,&#39;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">ENCLOSED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;\&#34;&#39;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="n">LINES</span><span class="w"> </span><span class="n">TERMINATED</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="s1">&#39;\\r\\n&#39;</span><span class="p">;</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div><h3 id="日志">日志</h3>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre tabindex="0" class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span></code></pre></td>
<td class="lntd">
<pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql"><span class="line"><span class="cl"><span class="c1">-- 首先查看日志是否开启了记录
</span></span></span><span class="line"><span class="cl"><span class="c1">-- 查看日志功能设置状态
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">show</span><span class="w"> </span><span class="n">variables</span><span class="w"> </span><span class="k">like</span><span class="w"> </span><span class="s1">&#39;general_log&#39;</span><span class="p">;</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- 打开日志记录功能
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">set</span><span class="w"> </span><span class="k">global</span><span class="w"> </span><span class="n">general_log</span><span class="o">=</span><span class="k">on</span><span class="p">;</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- 关闭日志记录功能
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">set</span><span class="w"> </span><span class="k">global</span><span class="w"> </span><span class="n">general_log</span><span class="o">=</span><span class="k">off</span><span class="p">;</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- 查看当前日志输出类型：table / file ，可根据需要具体设置
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">show</span><span class="w"> </span><span class="n">variables</span><span class="w"> </span><span class="k">like</span><span class="w"> </span><span class="s1">&#39;log_output&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- 设置日志输出至table
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">set</span><span class="w"> </span><span class="k">global</span><span class="w"> </span><span class="n">log_output</span><span class="o">=</span><span class="s1">&#39;table&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- 日志输出至table模式，查看日志记录
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">from</span><span class="w"> </span><span class="n">mysql</span><span class="p">.</span><span class="n">general_log</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">event_time</span><span class="w"> </span><span class="k">DESC</span><span class="w"> </span><span class="k">limit</span><span class="w"> </span><span class="mi">0</span><span class="p">,</span><span class="mi">5</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- 设置日志输出至file
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">set</span><span class="w"> </span><span class="k">global</span><span class="w"> </span><span class="n">log_output</span><span class="o">=</span><span class="s1">&#39;file&#39;</span><span class="p">;</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- 查看日志输出文件的保存路径
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">show</span><span class="w"> </span><span class="n">variables</span><span class="w"> </span><span class="k">like</span><span class="w"> </span><span class="s1">&#39;general_log_file&#39;</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- 修改日志输出文件的保存路径
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">set</span><span class="w"> </span><span class="k">global</span><span class="w"> </span><span class="n">general_log_file</span><span class="o">=</span><span class="s1">&#39;tmp/general.log&#39;</span><span class="p">;</span><span class="w"> 
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- 日志输出至table模式，清空日志记录
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="k">truncate</span><span class="w"> </span><span class="k">table</span><span class="w"> </span><span class="n">mysql</span><span class="p">.</span><span class="n">general_log</span><span class="p">;</span><span class="w">
</span></span></span><span class="line"><span class="cl"><span class="w"></span><span class="c1">-- 日志输出至file模式，查看日志记录
</span></span></span><span class="line"><span class="cl"><span class="c1"></span><span class="n">cat</span><span class="w"> </span><span class="o">/</span><span class="n">tmp</span><span class="o">/</span><span class="k">general</span><span class="p">.</span><span class="n">log</span><span class="w">
</span></span></span></code></pre></td></tr></table>
</div>
</div></div><div class="post-footer" id="post-footer">
    <div class="post-info">
        <div class="post-info-line">
            <div class="post-info-mod">
                <span>更新于 2023-09-18</span>
            </div></div>
        <div class="post-info-line">
            <div class="post-info-md"><span>
                            <a class="link-to-markdown" href="/mysql%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/index.md" target="_blank">阅读原始文档</a>
                        </span></div>
            <div class="post-info-share">
                <span><a href="javascript:void(0);" title="分享到 微博" data-sharer="weibo" data-url="http://example.org/mysql%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/" data-title="MySql 常用命令" data-image="/images/cover/mysql.png"><i class="fab fa-weibo fa-fw" aria-hidden="true"></i></a><a href="javascript:void(0);" title="分享到 百度" data-sharer="baidu" data-url="http://example.org/mysql%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/" data-title="MySql 常用命令"><i data-svg-src="/lib/simple-icons/icons/baidu.min.svg" aria-hidden="true"></i></a></span>
            </div>
        </div>
    </div>

    <div class="post-info-more">
        <section class="post-tags"><i class="fas fa-tags fa-fw" aria-hidden="true"></i>&nbsp;<a href="/tags/mysql/">Mysql</a></section>
        <section>
            <span><a href="javascript:void(0);" onclick="window.history.back();">返回</a></span>&nbsp;|&nbsp;<span><a href="/">主页</a></span>
        </section>
    </div>

    <div class="post-nav"><a href="/linux-swap%E5%88%86%E5%8C%BA/" class="prev" rel="prev" title="Linux swap 分区配置"><i class="fas fa-angle-left fa-fw" aria-hidden="true"></i>Linux swap 分区配置</a>
            <a href="/git%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4/" class="next" rel="next" title="Git 常用命令">Git 常用命令<i class="fas fa-angle-right fa-fw" aria-hidden="true"></i></a></div>
</div>
<div id="comments"></div></article></div>
            </main><footer class="footer">
        <div class="footer-container"><div class="footer-line" itemscope itemtype="http://schema.org/CreativeWork"><i class="far fa-copyright fa-fw" aria-hidden="true"></i><span itemprop="copyrightYear">2023</span><span class="author" itemprop="copyrightHolder">&nbsp;<a href="/" target="_blank">bytesc</a></span><span class="icp-splitter">&nbsp;|&nbsp;</span><br class="icp-br"/>
                    <span class="icp"><a class="text-reset" href="https://beian.miit.gov.cn/" target="_blank">沪ICP备2023015078号-2</a></span></div>
        </div>
    </footer></div>

        <div id="fixed-buttons"><a href="#" id="back-to-top" class="fixed-button" title="回到顶部">
                <i class="fas fa-arrow-up fa-fw" aria-hidden="true"></i>
            </a><a href="#" id="view-comments" class="fixed-button" title="查看评论">
                <i class="fas fa-comment fa-fw" aria-hidden="true"></i>
            </a>
        </div><link rel="stylesheet" href="/lib/lightgallery/css/lightgallery-bundle.min.css"><script type="text/javascript" src="/lib/lazysizes/lazysizes.min.js"></script><script type="text/javascript" src="/lib/lightgallery/lightgallery.min.js"></script><script type="text/javascript" src="/lib/lightgallery/plugins/thumbnail/lg-thumbnail.min.js"></script><script type="text/javascript" src="/lib/lightgallery/plugins/zoom/lg-zoom.min.js"></script><script type="text/javascript" src="/lib/clipboard/clipboard.min.js"></script><script type="text/javascript" src="/lib/sharer/sharer.min.js"></script><script type="text/javascript" src="/lib/typeit/index.umd.js"></script><script type="text/javascript">window.config={"code":{"copyTitle":"复制到剪贴板","maxShownLines":50},"comment":{},"data":{"id-1":"BYTESC","id-2":"BYTESC"},"lightgallery":true,"typeit":{"cursorChar":"|","cursorSpeed":1000,"data":{"id-1":["id-1"],"id-2":["id-2"]},"duration":-1,"speed":100}};</script><script type="text/javascript" src="/js/theme.min.js"></script></body>
</html>
